#include "mes.h"
#include<QSqlDatabase>
#include<QDebug>
#include<QSqlQuery>
#include<QSqlError>
#include<QSqlRecord>
#include<QMessageBox>
#include<QProcess>
#include<QSqlQueryModel>
#include<QTableWidget>
#include<QComboBox>
#include<QSqlTableModel>
#include<QJsonParseError>
#include<QJsonArray>
#include<QJsonDocument>
#include<QJsonObject>
#include<QDate>
#include<QtNetwork/QNetworkRequest>
#include<QtNetwork/QNetworkAccessManager>
#include<QtNetwork/QNetworkReply>
#include<QtNetwork/QNetworkCookieJar>
#include<QEventLoop>
#include<QObject>
#include<QSet>

Mes* Mes::mesInstance=nullptr;

NetworkCookie::NetworkCookie(QObject *parent)
    : QNetworkCookieJar(parent)
{

}

NetworkCookie::~NetworkCookie()
{

}

QList<QNetworkCookie> NetworkCookie::getCookies()
{
    return allCookies();
}

void NetworkCookie::setCookies(const QList<QNetworkCookie>& cookieList)
{
    if(this == NULL)
        return;
    this->setAllCookies(cookieList);
}

bool Mes::insertData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool Mes::initDatabase()
{
    QString SQL_CREATE_LOGININFO=QString("create table if not exists loginInfo("
    "user text primary key unique,password text,loginTime date)");

    QString SQL_CREATE_USERINFO=QString("create table if not exists userInfo("
                                        "workerno text primary key unique,workername text,password text,serverlevel text)");

    if (db.open())
    {
        QSqlQuery sql_query;

        //创建表loginInfo
        qInfo("[SQL] %s",qPrintable(SQL_CREATE_LOGININFO));
        if(!sql_query.exec(SQL_CREATE_LOGININFO))
        {
            qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
            return false;
        }
        //创建表userInfo
        qInfo("[SQL] %s",qPrintable(SQL_CREATE_USERINFO));
        if(!sql_query.exec(SQL_CREATE_USERINFO))
        {
            qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
            return false;
        }

        return true;
    }
    else
    {
        qWarning("Init database failed,database is not open.");
        return false;
    }
}

bool Mes::deleteData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool Mes::updateData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool Mes::selectData(const QString sqlStr,QList<QMap<QString,QVariant>> &valuesList)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    for( int r=0; sql_query.next(); r++ )
    {
        QSqlRecord rec = sql_query.record();
        QMap<QString,QVariant> valueMap;
        valueMap.clear();

        for( int c=0; c<rec.count(); c++ )
        {
            valueMap[rec.fieldName(c)] = rec.value(c);
        }
        valuesList.append(valueMap);
    }

    return true;
}

void Mes::quit()
{
    if(db.isOpen()){
        db.close();
        qInfo("Close database success.");
    }
    qInfo("[OK] AppDatabase quit.");
}

bool Mes::pingIsPass(QString ip){
    QProcess pingProcess;
    QString strArg = "ping " + ip + " -n 1";        // 利用Ping判断在线
//    QString strArg = "ping " + ip + " -n 1 -i 2";        // 利用Ping判断在线
//    QString strArg="ipconfig";
//    qDebug()<<strArg;
    pingProcess.start(strArg,QIODevice::ReadOnly);
    pingProcess.waitForFinished(1000);
    QString p_stdout=QString::fromLocal8Bit(pingProcess.readAllStandardOutput());
//    qDebug()<<p_stdout;

//    if(p_stdout.contains("powerleader")){
//        return true;
//    }else{
//        return false;
//    }

    if(p_stdout.contains("TTL=")){
        return true;
    }else{
        return false;
    }
}

//连接sqlserver数据库
bool Mes::openDatabase()
{
    this->workNumber.clear();
    this->role.clear();

    QString hostName="192.168.8.34";
    if(!pingIsPass(hostName)){
        return false;
    }

    QString name = QSqlDatabase::database().connectionName();
    if(QSqlDatabase::contains(name)){
        qDebug()<<"dataIsExist";
        db=QSqlDatabase::database(name);
        return true;
    }

    db=QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName(QString("DRIVER={SQL SERVER};"
                               "SERVER=%1;"        //服务器名称
                               "DATABASE=%2;"      //数据库名
                               "UID=%3;"           //登录名
                               "PWD=%4;"           //密码
                               )
                       .arg(hostName)
                       .arg("dm-data")
                       .arg("gcb")
                       .arg("ningbo..")
                       );
    if (!db.open())
    {
        qDebug()<<"connect sql server failed!";
//        QMessageBox::warning(this, QString("错误"), QString("数据库连接失败"))
        return false;
    }
    else
    {
        qDebug()<<"connect sql server successfully!";
        return true;
    }
}

QList<QMap<QString,QVariant>> Mes::getRoleName(const QString workNumber)  //由工号得到，人名，权限，权限名称
{
    QList<QMap<QString,QVariant>> valuesList;
    QString sql=QString("select * from (select a.account,a.name,a._role,b.name as ali from (select a.account,a.name,b._role from [r9-bos-101].[dbo].[oper_user] as a inner join [r9-bos-101].[dbo].[oper_role_user] as b on a.account=b._user) as a left join "
        "[r9-bos-101].[dbo].[oper_role] as b on a._role=b.code) as a where a.account=%1").arg("'"+workNumber+"'");
    selectData(sql,valuesList);
//    qDebug()<<valuesList;
    return valuesList;
}

bool Mes::getRole(const QString &workNumber)
{
    this->workNumber.clear();
    this->role.clear();

    QSqlQuery sql_query;
    QString sqlStr=QString("SELECT [_user],[_role] "
            "FROM [r9-bos-101].[dbo].[oper_role_user] where [_user]='%1' order by [_role] asc").arg(workNumber);
//    qDebug()<<sqlStr;

    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    bool isPass=false;

    while(sql_query.next()){
        isPass=true;
        this->workNumber=sql_query.value(0).toString();
        this->role.push_back(sql_query.value(1).toString());
    }
//    qDebug()<<this->workNumber<<","<<this->role;   // "7711","QMS-03"

    if(isPass){
        return true;
    }else{
        return false;
    }
}

bool Mes::workNumberIsExist(const QString &tmp)
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select account from [r9-bos-101].[dbo].[oper_user] where account='%1'").arg(tmp);
//    qDebug()<<"Exist:"<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return sql_query.next();
}

bool Mes::loginApi(QString& msg,QString workNumber,QString password)
{
    QNetworkRequest request;
    QUrl url=QUrl(QString("http://192.168.8.34:8950/api/station/login?username=%1&password=%2&workStation=SP110&workStationCode=1272013128368885760&barcode=&sob=101").arg(workNumber).arg(password));
    request.setUrl(url);
    request.setRawHeader("Content-Type","application/json; charset=utf-8");
    request.setRawHeader("x-request-datasource","101");
    request.setRawHeader("User-Agent","Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.97 Safari/537.36");
    QNetworkAccessManager manager;
    NetworkCookie *networkCookie=new NetworkCookie(nullptr);
    manager.setCookieJar(networkCookie);
    manager.post(request,"");
    QEventLoop eventLoop;
    connect(&manager, SIGNAL(finished(QNetworkReply*)), &eventLoop, SLOT(quit()));
    eventLoop.exec();
    QList<QNetworkCookie> listcookie=networkCookie->getCookies();
    QVariant var;
    var.setValue(listcookie);
    request.setHeader(QNetworkRequest::CookieHeader,var);
    QNetworkReply *reply=manager.post(request,"");
    connect(&manager, SIGNAL(finished(QNetworkReply*)), &eventLoop, SLOT(quit()));
    eventLoop.exec();
    QByteArray replyData=reply->readAll();
    if(reply->attribute(QNetworkRequest::HttpStatusCodeAttribute).toInt()==200){
        QMap<QString, QVariant> map;
        qDebug()<<QString().fromUtf8(replyData);
        if(jsonToListMap(QString().fromUtf8(replyData),map)){
            msg=map["message"].toString();
            if(msg.toLower()==QString("ok").toLower()){
                this->name=map["employeeName"].toString();
                return true;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }else{
        return false;
    }
}

bool Mes::jsonToListMap(const QString &msg, QMap<QString, QVariant> &temp_map)
{
    QJsonParseError json_error;
    QJsonObject json = QJsonDocument::fromJson(msg.toUtf8(), &json_error).object();
    if (json_error.error == QJsonParseError::NoError) {
        for(int i=0;i<json.size();i++){
            QString tmp = json.keys().at(i);
            QVariant tp = json.value(tmp).toVariant();
            temp_map.insert(tmp, tp);
        }
        return true;
    } else {
        qWarning() << "the json analysisError! "
                   "jsonError:" << json_error.error;
        return false;
    }
}

bool Mes::getRoleHash(const QString &workNumber){
    this->workNumber.clear();
    this->role.clear();

    QSqlQuery sql_query;
    QString sqlStr=QString("select b._user,c.roleMap from (select * from [r9-bos-101].[dbo].[oper_role_user] where _user='%1') as b inner join [dm-data].[dbo].[role] as c on b._role=c.code").arg(workNumber);

    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    bool isPass=false;

    while(sql_query.next()){
        isPass=true;
        this->workNumber=sql_query.value(0).toString();
        this->role.push_back(sql_query.value(1).toString());
    }
//    qDebug()<<this->workNumber<<","<<this->role;    // "7711","管理员"，"经理"，"组长"，"员工"

    if(isPass){
        return true;
    }else{
        return false;
    }
}

bool Mes::roleTable(QTableWidget *table)
{
    roleChangeText.clear();

    QSqlQuery sql_query;
    QString sqlStr=QString("SELECT [name],[roleMap] FROM [dm-data].[dbo].[role]");

    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    QStringList header={"职位","权限"};
    table->setHorizontalHeaderLabels(header);
    for(int i=0;sql_query.next();i++){
        for(int j=0;j<2;j++){
            if(j==0){
                table->setItem(i,j,new QTableWidgetItem(sql_query.value(j).toString()));
                continue;
            }
            if(j==1){
                QStringList tmpList;
                QString oldValue=sql_query.value(j).toString();
                tmpList<<oldValue;
                this->roleList.removeOne(oldValue);
                tmpList.append(this->roleList);
                QComboBox *comboBox=new QComboBox;
                comboBox->setObjectName("comboBox"+QString(i));
                comboBox->addItems(tmpList);
                table->setCellWidget(i,j,comboBox);
                connect(comboBox,SIGNAL(currentIndexChanged(const QString &)),this,SLOT(comboxBoxChange(const QString &)));
            }
        }
    }
    return true;
}

void Mes::comboxBoxChange(const QString &text){
    QComboBox *comboxSender=qobject_cast<QComboBox*>(sender());
    int i=QString(*(comboxSender->objectName().cend())).toInt();
    roleChangeText.insert(i,text);
}

QMap<QString,QString> Mes::getClassMap()
{
    QMap<QString,QString> classMapTmp;

    QSqlQuery sql_query;
    QString sqlStr=QString("SELECT [name],[code] FROM [dm-data].[dbo].[classificationForm]");
//    qDebug()<<sqlStr;

    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return classMapTmp;
    }

    while(sql_query.next()){
        if(!classMapTmp.contains(sql_query.value(0).toString())){
            classMapTmp.insert(sql_query.value(0).toString(),sql_query.value(1).toString());
        }
    }
//    qDebug()<<classMapTmp;
    return classMapTmp;
}

QStringList Mes::getCheckItemList(QString col,QString tableName)
{
    QStringList itemList;
    itemList<<"";

    QSqlQuery sql_query;
    QString sqlStr=QString("SELECT [%1] FROM [dm-data].[dbo].[%2]").arg(col).arg(tableName);

    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return itemList;
    }

    while(sql_query.next()){
        itemList<<sql_query.value(0).toString();
    }
    return itemList;
}

bool Mes::insertOneData(const QString tableName,const QStringList &lcolumn,const QStringList &lvalues)
{
    QString colNames=lcolumn.join(",");
    QString values=lvalues.join(",");

    QSqlQuery sql_query;
    QString sqlStr=QString("insert into %1 (%2) values (%3)").arg(tableName).arg(colNames).arg(values);
//    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::updateImport(const QString tableName,const QString NO,const QString remarks)
{
    QSqlQuery sql_query;
    QString sqlUpdate=QString("update %1 set status='已验收' where NO='%2'").arg(tableName).arg(NO);
    QString sqlInsert=QString("insert into [dm-data].[dbo].[inspectForm] (NO,inspectWorkNumber,inspectDate,remarks) values ('%1','%2',GETDATE(),'%3')").arg(NO,this->workNumber,remarks);

    this->db.transaction();
    sql_query.exec(sqlUpdate);
    sql_query.exec(sqlInsert);

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::updateImportFail(const QString tableName,const QString NO,const QString remarks)
{
    QSqlQuery sql_query;
    QString sqlUpdate=QString("update %1 set status='验收失败' where NO='%2'").arg(tableName).arg(NO);
    QString sqlInsert=QString("insert into [dm-data].[dbo].[inspectForm] (NO,inspectWorkNumber,inspectDate,remarks) values ('%1','%2',GETDATE(),'%3')").arg(NO,this->workNumber,remarks);

    this->db.transaction();
    sql_query.exec(sqlUpdate);
    sql_query.exec(sqlInsert);

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::NOIsExist(QString NO,QString workNumber,int &useAccount)
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select useAccount from [dm-data].[dbo].[userForm] where NO='%1' and useWorkNumber='%2'").arg(NO).arg(workNumber);
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    bool flag=false;
    if(sql_query.next()){
        flag=true;
        useAccount=sql_query.value(0).toInt();
    }
    return flag;
}

bool Mes::insertAndUpdateUserForm(const QMap<QString,QVariant>& userMap,const QMap<QString,QVariant>& checkMap,const QMap<QString,QVariant>& recheckMap,const QMap<QString,QVariant>& maintainMap)
{
    QSqlQuery sql_query;
    int useAccount;
    if(NOIsExist(userMap["NO"].toString(),userMap["useWorkNumber"].toString(),useAccount)){
        useAccount+=userMap["useAccount"].toInt();
        QString updateImport=QString("update [dm-data].[dbo].[importForm] set account=%1 where NO='%2'").arg(userMap["account"].toInt()).arg(userMap["NO"].toString());
        QString updateUseAccount=QString("update [dm-data].[dbo].[userForm] set useAccount=%1 where \
            NO='%2' and useWorkNumber='%3'").arg(useAccount).arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString());
        this->db.transaction();
        sql_query.exec(updateImport);
        sql_query.exec(updateUseAccount);
        if(!this->db.commit()){
            this->db.rollback();
            QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
            qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
            return false;
        }else{
            return true;
        }
    }else{
        QString updateImport=QString("update [dm-data].[dbo].[importForm] set account=%1 where NO='%2'").arg(userMap["account"].toInt()).arg(userMap["NO"].toString());

        this->db.transaction();
        sql_query.exec(updateImport);

        if(checkMap.isEmpty() && recheckMap.isEmpty() && maintainMap.isEmpty()){
            QString insertUserForm=QString("insert into [dm-data].[dbo].[userForm] ([NO] \
                                           ,[useWorkNumber] \
                                           ,[useAccount] \
                                           ,[overTime] \
                                           ,[agent] \
                                           ,[date] \
                                           ,[station],[threeModel]) values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','使用中')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                    userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString());
    //        qDebug()<<userMap["useAccount"].toInt();
    //        qDebug()<<insertUserForm;
            sql_query.exec(insertUserForm);
        }else{
            if(!checkMap.isEmpty()){
                QString insertCheckForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                        userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                        checkMap["threeModel"].toString()).arg(checkMap["noticeTime"].toString()).arg(checkMap["cycle"].toInt()).arg(checkMap["items"].toString()).arg(checkMap["checkWorkNumber"].toString());
    //            qDebug()<<insertCheckForm;
                sql_query.exec(insertCheckForm);
            }

            if(!recheckMap.isEmpty()){
                QString insertreCheckForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                        userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                        recheckMap["threeModel"].toString()).arg(recheckMap["noticeTime"].toString()).arg(recheckMap["cycle"].toInt()).arg(recheckMap["items"].toString()).arg(recheckMap["checkWorkNumber"].toString());
    //            qDebug()<<insertreCheckForm;
                sql_query.exec(insertreCheckForm);
            }

            if(!maintainMap.isEmpty()){
                QString insertMaintainForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                        userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                        maintainMap["threeModel"].toString()).arg(maintainMap["noticeTime"].toString()).arg(maintainMap["cycle"].toInt()).arg(maintainMap["items"].toString()).arg(maintainMap["checkWorkNumber"].toString());
    //            qDebug()<<insertMaintainForm;
                sql_query.exec(insertMaintainForm);
            }
        }

        if(!this->db.commit()){
            this->db.rollback();
            QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
            qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
            return false;
        }else{
            return true;
        }
    }
}

int Mes::getCountCheckTable(const QString& threeModel)
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select count(NO) from [dm-data].[dbo].[userForm] where useWorkNumber='%1' and threeModel='%2'").arg(this->workNumber).arg(threeModel);
//    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return 0;
    }

    if(sql_query.next()){
//        qDebug()<<sql_query.value(0).toInt();
        return sql_query.value(0).toInt();
    }
    return 0;
}

bool Mes::JsonToListList(const QString &msg, QList<QList<QVariant>> &list)
{
    QJsonParseError json_error;
    QJsonDocument parse_document = QJsonDocument::fromJson(msg.toUtf8(), &json_error);
    if (json_error.error == QJsonParseError::NoError) {
        if (parse_document.isArray()) {
            QJsonArray json_array1 = parse_document.array();
            qint32 size = json_array1.size();
            for (qint32 i = 0; i < size; ++i) {
                QList<QVariant> temp_list;
                QJsonArray json_array2 = json_array1.at(i).toArray();
                for (qint32 j = 0; j < json_array2.size(); ++j) {
                    temp_list.append(json_array2.at(j).toVariant());
                }
                list.append(temp_list);
            }
            return true;
        } else {
            qWarning() << "the json is not array error! "
                       "jsonError:" << json_error.error;
            return false;
        }
    } else {
        qWarning() << "the json analysisError! "
                   "jsonError:" << json_error.error;
        return false;
    }
}

bool Mes::ListListToJson(const QList<QList<QVariant>> &list, QString &msg)
{
    QJsonArray json_array;
    for (qint32 i = 0; i < list.size(); ++i) {
        QList<QVariant> temp_list = list.at(i);
        QJsonArray temp_array;
        for (qint32 j = 0; j < temp_list.size(); ++j) {
            temp_array.append(temp_list.at(j).toString());
        }
        json_array.append(temp_array);
    }
    QJsonDocument parse_doucment(json_array);
    msg = parse_doucment.toJson(QJsonDocument::Compact);
    return true;
}

QMap<QString,QList<QList<QVariant>>> Mes::getItemMap(const QString& threeMode)
{
    QMap<QString,QList<QList<QVariant>>> itemMap;
    if(restDateIsExist()){
        return itemMap;
    }

    QSqlQuery sql_query;
    QString sqlStr=QString("select a.NO,a.date,a.noticeTime,a.cycle,a.items,b.norms,a.checkWorkNumber \
        from [dm-data].[dbo].[userForm] as a inner join [dm-data].[dbo].[importForm] as b \
        on a.NO=b.NO where checkWorkNumber='%1' and threeModel='%2'").
        arg(this->workNumber).arg(threeMode);
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return itemMap;
    }

    while(sql_query.next()){            // true->符合周期，需要点检
        if(dateFilterNoMes(sql_query.value(1).toDateTime(),sql_query.value(3).toInt(),threeMode,
                           sql_query.value(0).toString(),sql_query.value(6).toString())){
            QString key=sql_query.value(5).toString()+"("+sql_query.value(0).toString()+")";
            QList<QList<QVariant>> valist;
            JsonToListList(sql_query.value(4).toString(),valist);
            itemMap.insert(key,valist);
        }
    }
    qDebug()<<itemMap;
    return itemMap;
}

bool Mes::dateIsExist(QString date,QString threeMode)
{
    Q_UNUSED(date);
    QSqlQuery sql_query;
    QString sqlStr=QString("select [date],[threeMode] from [dm-data].[dbo].[checkRecord] where DATEDIFF(dd,date,GETDATE())=0 and threeMode='%1'").arg(threeMode);
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
//    qDebug()<<"nopass:"<<sql_query.next();
    return sql_query.next();
}

bool Mes::restDateIsExist()
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select [restDate] from [dm-data].[dbo].[restDate] where DATEDIFF(dd,restDate,GETDATE())=0");
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
//    qDebug()<<"nopass:"<<sql_query.next();
    return sql_query.next();
}

bool Mes::dateIsExistWorkNumber(QString date,QString threeMode,QString NO)
{
    Q_UNUSED(date);
    QSqlQuery sql_query;
    QString sqlStr=QString("select NO from [dm-data].[dbo].[checkRecord] where DATEDIFF(dd,date,GETDATE())=0 and threeMode='%1' and NO='%2'").arg(threeMode).arg(NO);
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return sql_query.next();
}

bool Mes::dateIsExistWorkNumber(QString date,QString threeMode,QString NO,QString checkWorkNumber)
{
    Q_UNUSED(date);
    QSqlQuery sql_query;
    QString sqlStr=QString("select NO,workNumber from checkRecord where DATEDIFF(dd,date,GETDATE())=0 and   \
            threeMode='%1' and NO='%2' and workNumber='%3'").arg(threeMode).arg(NO).arg(checkWorkNumber);
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return sql_query.next();
}

bool Mes::dateFilterNoMes(QDateTime dtmp,int cycle,QString threeMode,QString NO,QString checkWorkNumber)     // 只跳过星期六、星期日
{
    // QString weekDay=QDate::currentDate().toString("ddd");

    uint etime=QDateTime::currentDateTime().toTime_t();
    uint stime = dtmp.toTime_t();               // dtmp为初始日期
    int ndaysec = 24*60*60;
    stime=stime-stime%ndaysec;
    int day=((etime - stime)-(etime-stime)%ndaysec)/(ndaysec);
    qDebug()<<dtmp;
    qDebug()<<etime<<"-"<<stime<<"="<<day;

    if(day%cycle==0){
        if(dateIsExistWorkNumber(QDate::currentDate().toString("yyyy-MM-dd"),threeMode,NO,checkWorkNumber)){
            return false;
        }else{
            return true;
        }
    }else{
        return false;
    }
}

//bool Mes::dateFilter(QDateTime dtmp,int cycle)      // 通过MES跳过法定节假日
//{
//    // QString weekDay=QDate::currentDate().toString("ddd");
//    if(restDateIsExist()){
//        return false;
//    }

//    QSqlQuery sql_query;
//    QString sqlStr=QString("SELECT [restDate] FROM [dm-data].[dbo].[restDate] where restDate='%1'").arg(QDate::currentDate().toString("yyyy-MM-dd"));
//    sql_query.exec(sqlStr);
//    if(sql_query.next()){
//        return false;
//    }

//    uint etime=QDateTime::currentDateTime().toTime_t();
//    uint stime = dtmp.toTime_t();
//    int ndaysec = 24*60*60;
//    stime=stime-stime%ndaysec;
//    int day=((etime - stime)-(etime-stime)%ndaysec)/(ndaysec);
//    QString dateSubSql=QString("SELECT count([restDate]) FROM [dm-data].[dbo].[restDate] where [restDate] between '%1' and '%2'").arg(dtmp.toString("yyyy-MM-dd"),QDate::currentDate().toString("yyyy-MM-dd"));
//    sql_query.exec(dateSubSql);
//    if(sql_query.next()){
//        int dayDiff=sql_query.value(0).toInt();
//        if((day-dayDiff)%cycle==0){
////            qDebug()<<true;
//            return true;
//        }else{
////            qDebug()<<false;
//            return false;
//        }
//    }else{
//        return false;
//    }
//}

bool Mes::insertCheckRecord(QMap<QString,QList<QList<QVariant>>>& recordMap,const QString& status,const QString& threeMode)
{
    Q_UNUSED(status);
    qDebug()<<recordMap;
    this->db.transaction();
    QSqlQuery sql_query;
    for(auto i=recordMap.begin();i!=recordMap.end();++i){
        QString statusFlag="正常";
        for(auto j=i.value().begin();j!=i.value().end();j++){
            if((*j)[1]=="异常"){
                statusFlag="异常";
                break;
            }
        }
        QString itemStatus;
        if(ListListToJson(i.value(),itemStatus)){
            QString sqlInsert=QString("insert into [dm-data].[dbo].[checkRecord] \
                    values ('%1','%2','%3','%4',GETDATE(),'%5')").arg(i.key(),this->workNumber,itemStatus,statusFlag,threeMode);
            qDebug()<<sqlInsert;
            if(!sql_query.exec(sqlInsert)) break;
        }
    }

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::checkSum(QDateTime dtmp,int cycle)  // 得到总数
{
    // QString weekDay=QDate::currentDate().toString("ddd");

    uint etime=QDateTime::currentDateTime().toTime_t();
    uint stime = dtmp.toTime_t();               // dtmp为初始日期
    int ndaysec = 24*60*60;
    stime=stime-stime%ndaysec;
    int day=((etime - stime)-(etime-stime)%ndaysec)/(ndaysec);

    if(day%cycle==0){
        return true;
    }else{
        return false;
    }
}

bool Mes::checkSum(QDateTime dtmp,int cycle,QString threeMode,QString NO,QString checkWorkNumber)   // 得到完成数
{
    // QString weekDay=QDate::currentDate().toString("ddd");
    uint etime=QDateTime::currentDateTime().toTime_t();
    uint stime = dtmp.toTime_t();               // dtmp为初始日期
    int ndaysec = 24*60*60;
    stime=stime-stime%ndaysec;
    int day=((etime - stime)-(etime-stime)%ndaysec)/(ndaysec);

    if(day%cycle==0){
        if(dateIsExistWorkNumber(QDate::currentDate().toString("yyyy-MM-dd"),threeMode,NO,checkWorkNumber)){
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }
}

bool Mes::checkSum(QDateTime dtmp,int cycle,QString threeMode,QString NO)   // 得到完成数
{
    // QString weekDay=QDate::currentDate().toString("ddd");
    uint etime=QDateTime::currentDateTime().toTime_t();
    uint stime = dtmp.toTime_t();               // dtmp为初始日期
    int ndaysec = 24*60*60;
    stime=stime-stime%ndaysec;
    int day=((etime - stime)-(etime-stime)%ndaysec)/(ndaysec);

    if(day%cycle==0){
        if(dateIsExistWorkNumber(QDate::currentDate().toString("yyyy-MM-dd"),threeMode,NO)){
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }
}

bool Mes::dateIsExist()
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select [date],[threeMode] from [dm-data].[dbo].[checkRecord] where DATEDIFF(dd,date,GETDATE())=0");
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return sql_query.next();
}

bool Mes::getCheckRecord(int& noPassCheck,int& noPassReCheck,int& noPassMaintain,
        int& passCheck,int& passReCheck,int& passMaintain,int& sumCheck,int& sumReCheck,int& sumMaintain)
{
    if(!getSumCheck(sumCheck,"点检")) return false;
    qDebug()<<"sumCheck"<<sumCheck;
    if(!getSumCheck(sumReCheck,"校验")) return false;
    qDebug()<<"sumReCheck"<<sumReCheck;
    if(!getSumCheck(sumMaintain,"保养")) return false;
    qDebug()<<sumMaintain;
    if(!getFinishCheck(passCheck,noPassCheck,"点检")) return false;
    qDebug()<<passCheck;
    if(!getFinishCheck(passReCheck,noPassReCheck,"校验")) return false;
    qDebug()<<"passReCheck"<<passReCheck;
    if(!getFinishCheck(passMaintain,noPassMaintain,"保养")) return false;
    qDebug()<<noPassMaintain;
    return true;
}

bool Mes::getSumCheck(int& sum,QString threeModel)
{
    sum=0;
    if(restDateIsExist()){
        return true;
    }

    QString sqlStr=QString("SELECT  [date]  \
                           ,[cycle],[NO] \
                       FROM [dm-data].[dbo].[userForm] where threeModel='%1'").arg(threeModel);
    QSqlQuery sql_query;
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    while(sql_query.next()){
        if(checkSum(sql_query.value(0).toDateTime(),sql_query.value(1).toInt())){
            sum++;
        }
    }
    return true;
}

bool Mes::getFinishCheck(int& finish,int& nopass,QString threeModel)
{
    finish=0;
    nopass=0;
    if(restDateIsExist()){
        return true;
    }

    QString sqlStr=QString("select b.NO,b.status,b.date,a.cycle,a.items,b.itemStatus,b.workNumber from   \
           (SELECT [NO]    \
                 ,[threeModel] \
                 ,[cycle],items  \
           from [userForm])a   \
           inner join  \
           (select * from  \
               (SELECT [NO]    \
                 ,[status] \
                 ,[date]   \
                 ,[threeMode],itemStatus,workNumber \
                 ,ROW_NUMBER() over(partition by NO,workNumber order by date desc) as rn \
           from [checkRecord] where threeMode='%1')r where r.rn=1)b \
           on a.NO=b.NO and a.threeModel=b.threeMode").arg(threeModel);
    qDebug()<<sqlStr;
    QSqlQuery sql_query;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    QSet<QString> finishSet,nopassSet;
    while(sql_query.next()){
        if(checkSum(sql_query.value(2).toDateTime(),sql_query.value(3).toInt(),threeModel,sql_query.value(0).toString(),
                    sql_query.value(6).toString())){
            QString tmp=sql_query.value(0).toString()+sql_query.value(6).toString();
            finishSet.insert(tmp);
            if(sql_query.value(1).toString()=="异常"){
                nopassSet.insert(tmp);
            }
        }
    }
    finish=finishSet.size();
    nopass=nopassSet.size();
    qDebug()<<"finish:"<<finish;
    return true;
}

bool Mes::NOworkNumberIsExist(QString NO,QString workNumber,int& useAccount){
    QString sqlStr=QString("select useAccount from userForm where NO='%1' and useWorkNumber='%2'").arg(NO,workNumber);
    QSqlQuery sql_query;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    if(sql_query.next()){
        useAccount=sql_query.value(0).toInt();
    }else{
        return false;
    }
}

bool Mes::accountInsertUpdate(QString NO,QString workNumber,int surplus,int scarpAccount,const QString& textReason,const QString& textVer)
{
    QString sqlUpdate=QString("update userForm set useAccount=%1 where NO='%2' and useWorkNumber='%3'").arg(surplus).arg(NO).arg(workNumber);
    QString sqlInsert=QString("insert into scrapForm values ('%1',%2,'%3','%4','%5',GETDATE())").arg(NO).arg(scarpAccount).arg(textReason).arg(textVer).arg(this->workNumber);
    QSqlQuery sql_query;
    this->db.transaction();
    sql_query.exec(sqlUpdate);
    sql_query.exec(sqlInsert);

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}


bool Mes::insertUpdateRepairForm(QString NO,QString workNumber,const QString& textReason)
{
    QString exsitsql=QString("select * from repairForm where status='维修中' and NO='%1' and userNumber='%2'").arg(NO,workNumber);
    QSqlQuery sql_query1;
    if(!sql_query1.exec(exsitsql))
    {
        qWarning("[SQL] %s",qPrintable(sql_query1.lastError().text()));
        return false;
    }

    if(sql_query1.next()){
        QMessageBox::information(NULL,QString("提示"),QString("该设备正在维修中，无需重复提交     "));
        return false;
    }

    QSqlQuery sql_query;
    QString sqlSelect=QString("select threeModel from userForm where NO='%1' and useWorkNumber='%2'").arg(NO,workNumber);
    qDebug()<<sqlSelect;
    if(!sql_query.exec(sqlSelect))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    QStringList modeList;
    while(sql_query.next()){
        modeList<<sql_query.value(0).toString();
    }
    QString modelStr=modeList.join(";");
//    QString sqlUpdate=QString("update userForm set threeModel='维修中' where NO='%2' and useWorkNumber='%3'").arg(NO).arg(workNumber);
//    qDebug()<<sqlUpdate;
    QString sqlInsert=QString("insert into repairForm values ('%1','%2','%3',GETDATE(),'维修中','%4','%5')").arg(NO).arg(this->workNumber).arg(textReason).arg(modelStr).arg(workNumber);
    this->db.transaction();
//    sql_query.exec(sqlUpdate);
    sql_query.exec(sqlInsert);
    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::repairVer(QString NO,const QString& remarks,QString modelStr,QString userNumber)
{
//    QString sqlUpdate=QString("update userForm set threeModel='已维修' where NO='%1' and useWorkNumber='%2'").arg(NO).arg(userNumber);
    QString sqlUpdateStatus=QString("update repairForm set status='维修历史' where status='维修中' and NO='%1' and workNumber='%2'").arg(NO).arg(userNumber);
    QString sqlInsert=QString("insert into repairForm values ('%1','%2','%3',GETDATE(),'已维修','%4','')").arg(NO).arg(this->workNumber).arg(
                remarks).arg(modelStr);
    QSqlQuery sql_query;
    this->db.transaction();
//    sql_query.exec(sqlUpdate);
    sql_query.exec(sqlUpdateStatus);
    qDebug()<<"updateStatus:"<<sqlUpdateStatus;
    sql_query.exec(sqlInsert);
    qDebug()<<"sqlInsert:"<<sqlInsert;
    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::getTableData(QList<QStringList> &tableData,QString threeModel,QString checkNameLine)
{
    if(restDateIsExist()){
        return false;
    }

    QString checkNameChinese;
    if(checkNameLine!=""){
        checkNameChinese=QString(" and f.name like '%%1%'").arg(checkNameLine);
    }

    QString sqlStr=QString("select e.NO,e.norms,e.checkWorkNumber,f.name,e.items,e.useWorkNumber,e.date,e.cycle \
            from (SELECT a.NO,b.norms,a.checkWorkNumber,a.items,a.useWorkNumber,a.date,a.cycle \
            FROM userForm as a inner join importForm as b on a.NO=b.NO and a.threeModel='%1')e inner join   \
            (select account,name from [r9-bos-101].[dbo].[oper_user])f on e.checkWorkNumber=f.account%2")
            .arg(threeModel).arg(checkNameChinese);
    QSqlQuery sql_query;
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    QMap<QString,QStringList> m1;
    while(sql_query.next()){
        QStringList tmp;
        if(checkSum(sql_query.value(6).toDateTime(),sql_query.value(7).toInt())){
            for(int i=0;i<5;i++){
                tmp<<sql_query.value(i).toString();
            }
        }else{
            continue;
        }
        QString key=sql_query.value(0).toString()+sql_query.value(5).toString();
        m1.insert(key,tmp);
    }

    qDebug()<<tableData;

    QString sqlStr1=QString("select b.NO,b.status,b.date,a.cycle,a.items,b.itemStatus,b.workNumber,a.checkWorkNumber \
            from (SELECT [NO]    \
                  ,[threeModel] \
                  ,[cycle],items,checkWorkNumber \
            from [userForm])a   \
            inner join  \
            (select * from  \
                (SELECT [NO]    \
                  ,[status] \
                  ,[date]   \
                  ,[threeMode],itemStatus,workNumber \
                  ,ROW_NUMBER() over(partition by NO,workNumber order by date desc) as rn \
            from [checkRecord] where threeMode='%1')r where r.rn=1)b \
            on a.NO=b.NO and a.threeModel=b.threeMode").arg(threeModel);
    qDebug()<<sqlStr1;
    QSqlQuery sql_query1;
    if(!sql_query1.exec(sqlStr1))
    {
        qWarning("[SQL] %s",qPrintable(sql_query1.lastError().text()));
        return false;
    }
    QMap<QString,QStringList> m2;
    while(sql_query1.next()){
        QStringList tmp;
        if(checkSum(sql_query1.value(2).toDateTime(),sql_query1.value(3).toInt(),threeModel,sql_query1.value(0).toString())){
            tmp<<sql_query1.value(5).toString();
            if(sql_query1.value(1).toString()=="异常"){
                tmp<<"异常";
            }else{
                tmp<<"正常";
            }
            tmp<<sql_query1.value(2).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            if(sql_query1.value(2).toDate()==QDate::currentDate()){
                tmp<<"完成";
            }else{
                tmp<<"未完成";
            }
        }else{
            tmp<<""<<""<<""<<"未完成";
        }
        QString key=sql_query1.value(0).toString()+sql_query1.value(6).toString();
        m2.insert(key,tmp);
    }

    int n=0;
    for(auto i=m1.begin();i!=m1.end();++i){
        if(m2.contains(i.key())){
            QStringList tmp;
            tmp<<i.value()<<m2[i.key()];
            tableData.append(tmp);
        }else{
            QStringList tmp;
            tmp<<i.value()<<QStringList{"","","","未完成"};
            tableData.append(tmp);
        }
        n++;
    }
    qDebug()<<tableData;
    if(n==0){
        return false;
    }else{
        return true;
    }
}

bool Mes::getName(QString workNumber,QString &newName)
{
    QString sqlStr=QString("SELECT TOP 1 [account]   \
                           ,[name]  \
                       FROM [r9-bos-101].[dbo].[oper_user] where account='%1'").arg(workNumber);
    QSqlQuery sql_query;
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    if(sql_query.next()){
        newName=sql_query.value(1).toString();
        return true;
    }else{
        return false;
    }
}

bool Mes::userChange(QString NO,QString oldWorkNumber,QString newWorkNumber,QString reasonText)
{
    QString sqlInsert=QString("insert into userChangeHistory values ('%1','%2','%3','%4',GETDATE(),'%5')").
            arg(NO).arg(oldWorkNumber).arg(newWorkNumber).arg(reasonText).arg(this->workNumber);
    QString sqlUpdate=QString("update userForm set useWorkNumber='%1' where NO='%2' and \
                              useWorkNumber='%3'").arg(newWorkNumber).arg(NO).arg(oldWorkNumber);
    qDebug()<<"sqlInsert:"<<sqlInsert;
    qDebug()<<"sqlUpdate:"<<sqlUpdate;
    QSqlQuery sql_query;

    this->db.transaction();
    sql_query.exec(sqlInsert);
    sql_query.exec(sqlUpdate);

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::checkChange(QString NO,QString oldWorkNumber,QString newWorkNumber,QString reasonText)
{
    QString sqlInsert=QString("insert into checkerChangeHistory values ('%1','%2','%3','%4',GETDATE(),'%5')").
            arg(NO).arg(oldWorkNumber).arg(newWorkNumber).arg(reasonText).arg(this->workNumber);
    QString sqlUpdate=QString("update userForm set checkWorkNumber='%1' where NO='%2' and \
                              useWorkNumber='%3'").arg(newWorkNumber).arg(NO).arg(oldWorkNumber);
    qDebug()<<"sqlInsert:"<<sqlInsert;
    qDebug()<<"sqlUpdate:"<<sqlUpdate;
    QSqlQuery sql_query;

    this->db.transaction();
    sql_query.exec(sqlInsert);
    sql_query.exec(sqlUpdate);

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::repairIsExist(QString NO,QString workNumber)
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select NO from repairForm where NO='%1' and userNumber='%2' and status='维修中'").
            arg(NO).arg(workNumber);
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return sql_query.next();
}

bool Mes::checkIsExist(QString NO,QString workNumber,bool &flag)
{
    QSqlQuery sql_query;
    QString sqlStr=QString("select a.* from (select threeModel FROM \
        [dm-data].[dbo].[userForm] where NO='%1' and useWorkNumber='%2')a where a.threeModel= \
        '点检' or a.threeModel='校验' or a.threeModel='保养'").arg(NO).arg(workNumber);
    qDebug()<<sqlStr;
    if(!sql_query.exec(sqlStr))
    {
        QMessageBox::warning(NULL, QString("错误"), QString("数据库连接失败\n")+sql_query.lastError().text());
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    if(sql_query.next()){
        flag=true;
    }else{
        flag=false;
    }
    return true;
}

bool Mes::addUserForm(const QMap<QString,QVariant>& userMap,const QMap<QString,QVariant>& checkMap,const QMap<QString,QVariant>& recheckMap,const QMap<QString,QVariant>& maintainMap)
{
    QSqlQuery sql_query;
    this->db.transaction();
    QString updateForm=QString("update userForm set threeModel='变更历史' where NO='%1' and \
        useWorkNumber='%2'").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString());
    sql_query.exec(updateForm);

    if(!checkMap.isEmpty()){
        QString insertCheckForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                checkMap["threeModel"].toString()).arg(checkMap["noticeTime"].toString()).arg(checkMap["cycle"].toInt()).arg(checkMap["items"].toString()).arg(checkMap["checkWorkNumber"].toString());
//            qDebug()<<insertCheckForm;
        sql_query.exec(insertCheckForm);
    }

    if(!recheckMap.isEmpty()){
        QString insertReCheckForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                recheckMap["threeModel"].toString()).arg(recheckMap["noticeTime"].toString()).arg(recheckMap["cycle"].toInt()).arg(recheckMap["items"].toString()).arg(recheckMap["checkWorkNumber"].toString());
//            qDebug()<<insertreCheckForm;
        sql_query.exec(insertReCheckForm);
    }

    if(!maintainMap.isEmpty()){
        QString insertMaintainForm=QString("insert into userForm values ('%1','%2',%3,%4,'%5',GETDATE(),'%6','%7','%8',%9,'%10','%11')").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString()).arg(
                userMap["useAccount"].toInt()).arg(userMap["overTime"].toInt()).arg(userMap["agent"].toString()).arg(userMap["station"].toString()).arg(
                maintainMap["threeModel"].toString()).arg(maintainMap["noticeTime"].toString()).arg(maintainMap["cycle"].toInt()).arg(maintainMap["items"].toString()).arg(maintainMap["checkWorkNumber"].toString());
//            qDebug()<<insertMaintainForm;
        sql_query.exec(insertMaintainForm);
    }

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}

bool Mes::updateUserForm(const QMap<QString,QVariant>& userMap,const QMap<QString,QVariant>& checkMap,const QMap<QString,QVariant>& recheckMap,const QMap<QString,QVariant>& maintainMap)
{
    if(addUserForm(userMap,checkMap,recheckMap,maintainMap)){
        return true;
    }else{
        return false;
    }
}

bool Mes::getUserData(const QMap<QString,QVariant>& userMap,QMap<QString,QVariant> &checkMap,QMap<QString,QVariant> &recheckMap,QMap<QString,QVariant> &maintainMap)
{
    QSqlQuery sql_query;
    this->db.transaction();
    QString checkString=QString("SELECT * FROM userForm where NO='%1' and useWorkNumber='%2' \
        and threeModel='点检'").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString());
    qDebug()<<checkString;
    sql_query.exec(checkString);
    if(sql_query.next()){
        QTime noticeTime=sql_query.value(8).toTime();
        int cycle=sql_query.value(9).toInt();
        QString items=sql_query.value(10).toString();
        QString checkWorkNumber=sql_query.value(11).toString();
        checkMap.insert("noticeTime",noticeTime);
        checkMap.insert("cycle",cycle);
        checkMap.insert("items",items);
        checkMap.insert("checkWorkNumber",checkWorkNumber);
    }

    QString recheckString=QString("SELECT * FROM userForm where NO='%1' and useWorkNumber='%2' \
        and threeModel='校验'").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString());
    sql_query.exec(recheckString);
    if(sql_query.next()){
        QTime noticeTime=sql_query.value(8).toTime();
        int cycle=sql_query.value(9).toInt();
        QString items=sql_query.value(10).toString();
        QString checkWorkNumber=sql_query.value(11).toString();
        recheckMap.insert("noticeTime",noticeTime);
        recheckMap.insert("cycle",cycle);
        recheckMap.insert("items",items);
        recheckMap.insert("checkWorkNumber",checkWorkNumber);
    }

    QString maintainString=QString("SELECT * FROM userForm where NO='%1' and useWorkNumber='%2' \
        and threeModel='保养'").arg(userMap["NO"].toString()).arg(userMap["useWorkNumber"].toString());
    sql_query.exec(maintainString);
    if(sql_query.next()){
        QTime noticeTime=sql_query.value(8).toTime();
        int cycle=sql_query.value(9).toInt();
        QString items=sql_query.value(10).toString();
        QString checkWorkNumber=sql_query.value(11).toString();
        maintainMap.insert("noticeTime",noticeTime);
        maintainMap.insert("cycle",cycle);
        maintainMap.insert("items",items);
        maintainMap.insert("checkWorkNumber",checkWorkNumber);
    }

    if(!this->db.commit()){
        this->db.rollback();
        QMessageBox::warning(NULL, QString("提交错误"), QString("数据库连接失败\n")+this->db.lastError().text());
        qWarning("[SQL] %s",qPrintable(this->db.lastError().text()));
        return false;
    }else{
        return true;
    }
}
